Technology

Excel and ODBC

This article is intended to give you a couple of lines advice when working with Excel through an ODBC connection.

What people forget when writing data to an excel sheet is to disable the read-only option during ODBC configuration. Press the options button to get the rest of the screen.

Defining a table

If you want your sheet to behave as a database table, you need to define a table name and indicate the range in your sheet that is applicable to that table. Therefore go to Insert - Name - Define...

In the dialog box you get enter the name of your table and select the range of cells you want in that table.

Defining the columns (Fields)

The first row in the range you selected (table definition) contains the name of the columns of your table. In our example this would be A1, B1 and C1.

Important: make sure your column names do not contain spaces or operators typical to excel (like - = / + $ )

What else goes wrong

• When writing lines make sure you table name definition is not pointing to an infinite range. Limit the number of rows in your selection to the last one containing data. If your sheet does not yet contain data, select only the first row containing the column names.
• A lot of problems are caused by mixing up numeric and string data. For some weird reason the excel ODBC does not return numeric information any more when some of the cells of the same column already contain alphanumeric data. To solve this you must add an accent (') at the beginning of each cell containing numeric data. By this the ODBC will treat them as alphanumeric.